package com.ztc.ZtcClassesPriceForSale.dao;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.aqgj.util.BaseDaoImpl;
import com.ztc.ZtcClassesPriceForSale.bean.ZtcClassesPriceForSale;
import com.ztc.ZtcClassesSiteForSale.bean.ZtcClassesSiteForSale;
import com.ztc.ZtcClassesSiteForSale.dao.ZtcClassesSiteForSaleDao;

/**
 * <p>
 * 
 * </p>
 * 
 * @ClassName ZtcClassesPriceForSaleDao
 * @JDK 1.5
 * @author
 * @date
 */
@Repository("ZtcClassesPriceForSaleDao")
public class ZtcClassesPriceForSaleDao extends BaseDaoImpl<ZtcClassesPriceForSale> {
	@Autowired
	ZtcClassesSiteForSaleDao ztcClassesSiteForSaleDao;

	public String save(ZtcClassesPriceForSale ztcClassesPriceForSale) {
		this.getHibernateTemplate().save(ztcClassesPriceForSale);
		return ztcClassesPriceForSale.getId();
	}
	public List<ZtcClassesPriceForSale> getAllZtcClassesPriceForSales() {
		return this.getAll();
	}

	public String delete(String id, String classesForSaleId, String modifyUserId) {
		String sql = "delete from ztc_classes_price_for_sale where 1=1";
		if (id != null) {
			sql += " and id='" + id + "'";
		}
		if (classesForSaleId != null) {
			sql += " and fk_classes_for_sale_id='" + classesForSaleId + "'";
		}
		if (modifyUserId != null) {
			sql += " and fk_modify_user_id='" + modifyUserId + "'";
		}
		if (this.currentSession().createNativeQuery(sql).executeUpdate() >= 0) {
			return "1";
		}
		return "0";
	}

	public List<ZtcClassesPriceForSale> searchInfoEquals(ZtcClassesPriceForSale ztcClassesPriceForSale) {
		String sql = "select * from ztc_classes_price_for_sale where 1=1";
		if (ztcClassesPriceForSale.getPrice() != null) {
			sql += " and price='" + ztcClassesPriceForSale.getPrice() + "'";
		}
		if (ztcClassesPriceForSale.getDistance() != null) {
			sql += " and distance='" + ztcClassesPriceForSale.getDistance() + "'";
		}
		if (ztcClassesPriceForSale.getStatus() != null) {
			sql += " and status='" + ztcClassesPriceForSale.getStatus() + "'";
		}
		if (ztcClassesPriceForSale.getRemainSeatNumber() != null) {
			sql += " and remain_seat_number='" + ztcClassesPriceForSale.getRemainSeatNumber() + "'";
		}
		if (ztcClassesPriceForSale.getRemainChildSeatNumber()!= null) {
			sql += " and remain_child_seat_number='" + ztcClassesPriceForSale.getRemainChildSeatNumber() + "'";
		}
		if (ztcClassesPriceForSale.getFkStartClassesSiteForSaleId() != null) {
			sql += " and fk_start_classes_site_for_sale_id='" + ztcClassesPriceForSale.getFkStartClassesSiteForSaleId()
					+ "'";
		}
		if (ztcClassesPriceForSale.getFkEndClassesSiteForSaleId() != null) {
			sql += " and fk_end_classes_site_for_sale_id='" + ztcClassesPriceForSale.getFkEndClassesSiteForSaleId()
					+ "'";
		}
		if (ztcClassesPriceForSale.getFkClassesForSaleId() != null) {
			sql += " and fk_classes_for_sale_id='" + ztcClassesPriceForSale.getFkClassesForSaleId() + "'";
		}
		if (ztcClassesPriceForSale.getId() != null) {
			sql += " and id='" + ztcClassesPriceForSale.getId() + "'";
		}
		List<ZtcClassesPriceForSale> list = this.currentSession().createNativeQuery(sql,ZtcClassesPriceForSale.class).getResultList();
		return list;
	}

	public List<ZtcClassesPriceForSale> searchInfoLikes(String startSiteName, String endSiteName,String departureDate) {
		String sql = "select * from ztc_classes_price_for_sale as price,ztc_classes_site_for_sale as start_site,ztc_classes_site_for_sale as end_site,ztc_classes_for_sale,ztc_classes"
				+ " where price.fk_start_classes_site_for_sale_id=start_site.id and price.fk_end_classes_site_for_sale_id=end_site.id"
		        + " and price.fk_classes_for_sale_id=ztc_classes_for_sale.id and ztc_classes_for_sale.fk_ztc_classes_id=ztc_classes.id";
		if (startSiteName != null) {
			sql += " and start_site.name like '%" + startSiteName + "%'";
		}
		if (endSiteName != null) {
			sql += " and end_site.name like '%" + endSiteName + "%'";
		}
		if(departureDate!=null){
			sql+=" and date_format(start_site.departure_time,'%Y-%m-%d')='"+departureDate+"'";
		}
		sql+=" and ztc_classes_for_sale.departure_date<= DATE_ADD(CURDATE(),INTERVAL ztc_classes.pre_sale_days DAY)";
		sql+=" and price.status='0'";
		sql+=" and ztc_classes_for_sale.ticket_status='0'";
		List<ZtcClassesPriceForSale> list = this.currentSession().createNativeQuery(sql,ZtcClassesPriceForSale.class).getResultList();
		return list;
	}

	public String update(ZtcClassesPriceForSale ztcClassesPriceForSale) {
		this.getHibernateTemplate().saveOrUpdate(ztcClassesPriceForSale);
		if (ztcClassesPriceForSale.getId() != null) {
			return "1";
		}
		return "0";
	}

	public List<ZtcClassesPriceForSale> getInfluencedClassesPriceForSale(ZtcClassesPriceForSale price) {
		ZtcClassesSiteForSale siteForSale = new ZtcClassesSiteForSale();
		siteForSale.setId(price.getFkStartClassesSiteForSaleId());
		ZtcClassesSiteForSale startSite = ztcClassesSiteForSaleDao.searchInfoEquals(siteForSale).get(0);
		siteForSale.setId(price.getFkEndClassesSiteForSaleId());
		ZtcClassesSiteForSale endSite = ztcClassesSiteForSaleDao.searchInfoEquals(siteForSale).get(0);
		String sql = "select * from ztc_classes_price_for_sale as price,ztc_classes_site_for_sale as start_site,ztc_classes_site_for_sale as end_site"
				+ " where price.fk_start_classes_site_for_sale_id=start_site.id and price.fk_end_classes_site_for_sale_id=end_site.id";
		sql += " and price.fk_classes_for_sale_id='" + price.getFkClassesForSaleId() + "'";
		sql += " and ((start_site.sequence<" + startSite.getSequence() + " and end_site.sequence>"+ startSite.getSequence() + ") or (";
		sql += " start_site.sequence>=" + startSite.getSequence() + " and start_site.sequence<"+ endSite.getSequence() + "))";
		return this.currentSession().createNativeQuery(sql,ZtcClassesPriceForSale.class).getResultList();
	}

	public List<ZtcClassesPriceForSale> searchOverduePriceForSale() {
		String sql = "select * from ztc_classes_price_for_sale where ztc_classes_price_for_sale.fk_classes_for_sale_id in\n" +
				"(select id from ztc_classes_for_sale where CURDATE()>=DATE_ADD(ztc_classes_for_sale.departure_date,INTERVAL 30 DAY))\n" +
				"and ztc_classes_price_for_sale.`type`=0";
		return this.currentSession().createNativeQuery(sql,ZtcClassesPriceForSale.class).getResultList();
	}

	public List<ZtcClassesPriceForSale> searchInfoEqualsSequence(String startSequence, String endSequence,String classesForSaleId) {
		String sql = "select * from ztc_classes_price_for_sale as price,ztc_classes_site_for_sale as start_site,ztc_classes_site_for_sale as end_site,ztc_classes_for_sale"
				+ " where price.fk_start_classes_site_for_sale_id=start_site.id and price.fk_end_classes_site_for_sale_id=end_site.id"
				+ " and price.fk_classes_for_sale_id=ztc_classes_for_sale.id"
			    + " and start_site.sequence = '" + startSequence + "'"
			    + " and end_site.sequence = '" + endSequence + "'";
		sql+=" and ztc_classes_for_sale.id='"+classesForSaleId+"'";
		List<ZtcClassesPriceForSale> list = this.currentSession().createNativeQuery(sql,ZtcClassesPriceForSale.class).getResultList();
		return list;
	}
}